﻿﻿use master
go
if exists(select * from sys.sysdatabases where name = 'MySchoolDB')
drop database MySchoolDB
create database MySchoolDB
on
(
	name = 'MySchoolDB_data',
	filename = 'E:\数据库文件\Y2\MySchoolDB_data.mdf',
	size = 5mb,
	maxsize = unlimited,
	filegrowth = 1mb
)
log on
(
	name = 'MySchoolDB_log',
	filename = 'E:\数据库文件\Y2\MySchoolDB_log.ldf',
	size = 3mb,
	maxsize = 20mb,
	filegrowth = 10%
)
go
use MySchoolDB
go
--创建学生表student
if exists(select * from sys.sysobjects where name = 'student')
drop table student
create table student
(
	st_id varchar(9) not null,	--学生学号
	st_nm varchar(8) not null,	--学生姓名
	st_sex varchar(2),	--学生性别
	st_birth datetime,	--出生日期
	st_score int,	--入学成绩
	st_date datetime,	--入学日期
	st_from varchar(20),	--学生来源
	st_dpid varchar(2),	--所在系编号
	st_mnt tinyint	--学生职务
)
go
alter table student add
constraint PK_student_st_id primary key(st_id)
go
--创建课程信息表couse
if exists(select *from sys.sysobjects where name = 'couse')
drop table couse
create table couse
(
	cs_id varchar(4) not null,	--课程编号
	cs_nm varchar(20) not null,	--课程名称
	cs_tm int,	--课程学时
	cs_sc int	--课程学分
)
go
alter table couse add
constraint PK_couse_cs_id primary key(cs_id)
go
--创建选课表slt_couse
if exists(select * from sys.sysobjects where name = 'slt_couse')
drop table slt_couse
create table slt_couse
(
	cs_id varchar(4) not null,	--课程编号
	st_id varchar(9) not null,	--学生编号
	score int,	--课程成绩
	sltdate datetime	--选课日期
)
go
alter table slt_couse add
constraint FK_slt_couse_couse_cs_id foreign key(cs_id) references couse(cs_id),
constraint FK_slt_couse_student_st_id foreign key(st_id) references student(st_id)
go
--创建院系信息表dept
if exists(select * from sys.sysobjects where name = 'dept')
drop table dept
create table dept
(
	dp_id varchar(2) not null,	--系编号
	dp_nm varchar(20) not null,	--院系名称
	dp_drt varchar(8),	--院系主任
	dt_tel varchar(12)	--联系电话
)
go


use MySchoolDB
go
--向dept表插入一条记录，系号11，系名自动控制系，系主任为李其余，电话81234567
insert into dept(dp_id,dp_nm,dp_drt,dt_tel) values('11','自动控制系','李其余','81234567')
--向student表插入一条记录，学号070201001，姓名为王小五，性别为男，出生日期为1990年9月9日，系号为11，其余字段为NULL或默认值
insert into student(st_id, st_nm, st_sex, st_birth , st_dpid)
values('070201001','王小五','男','1990-9-9','11')
--向couse表插入一条记录，课程号1234，课程名为操作系统，其余字段为NULL或默认值
insert into couse(cs_id, cs_nm, cs_tm, cs_sc) values('1234','操作系统',null,null)
--向slt_couse表插入一条记录，课程号1234，学名070201001，其余字段为NULL或默认值
insert into slt_couse(cs_id, st_id, score, sltdate) values('1234','070201001',null,'')



use MySchoolDB
go
--修改student表记录，将王小五的入学成绩改为88
update student set st_score = 88 where student.st_nm = '王小五'
--修改couse表记录，将所有记录的学分改为4，学时改为64
update couse set cs_sc=4,cs_tm=64
--修改slt_couse表记录，将课程号为1234，学名为070201001的记录的成绩改为77
update slt_couse set score=77 where cs_id='1234' AND st_id='070201001'


use MySchoolDB
go
--删除slt_couse表记录，将课程号为1234，学名为070201001的记录删除
delete from slt_couse where cs_id = '1234' and st_id = '070201001'
--删除couse表记录，将课程号为1234的记录删除
delete from couse where cs_id = '1234'



use MySchoolDB
go
--查询所有系的信息
select * from dept
--查询所有的课程号与课程名称
select cs_id,cs_nm from couse 
--在查询student表时使用列表达式：入学成绩+400
select st_id ,st_nm ,st_score ,st_score+400 as new_score from student
--使用AS关键字为dept表中属性指定列名：系号、系名、系主任、联系电话
select dp_id as 系号, dp_nm as 系名, dp_drt as 系主任, dt_tel as 联系电话 from dept
--使用"="号为couse表中属性指定列名：课程号、课程名、学时(=cs_sc*16)、学分
select 课程号=cs_id, 课程名=cs_nm, 学时=cs_tm*16, 学分=cs_sc from couse
--查询dept表的系号、系名和系主任，向查询结果中插入说明列：系号、系名和系主任
select '系号',dp_id, '系名',dp_nm, '系主任',dp_drt from dept
--显示所有学生的学号、姓名、性别和入学年份
select st_id, st_nm, st_sex, DATEPART(yy,st_date) as 入学年份 from student
--显示所有学生学号、姓名、性别和班级(学号前6位)
select st_id, st_nm, st_sex, LEFT(st_id,6) as 班级 from student
--显示所有学生班级,DISTINCT 用于返回唯一不同的值
select distinct LEFT(st_id,6) as 班级 from student
--显示前5条学生记录信息
select top 5 * from student
--显示前25%条学生记录信息
select top 25 percent * from student



use MySchoolDB
go
--查询dept表中系号为11的院系信息
select * from dept where dp_id = '11'
--查询student表中11系的学生学号、姓名、性别和所在系编号
select st_id, st_nm, st_sex, st_dpid from student where st_dpid = '11'
--查询student表中2008年及以后入学的学生信息
select * from student where DATEPART( yy, st_date )> 2008
--在查询student表080808班学生的学号、姓名、性别和入学成绩
select st_id, st_nm, st_sex, st_score from student where Left(st_id,6)='080808'
--查询student表中非11系的学生信息
select * from student where not (st_dpid = '11')
--查询选修了1002号课程且成绩在60以下的学生学号
select st_id from slt_couse where cs_id='1002' AND score<60
--查询2007年入学的11系所有男生信息
select * from student where DATEPART(yy,st_date)=2007 AND st_dpid='11' AND st_sex='男'
--查询11系和12系的学生信息
select * from student where st_dpid='11' and st_dpid='12'
--查询11系和12系所有2007年入学的学生信息
select * from  student where st_dpid='11' and st_dpid='12' and DATEPART(yy,st_date)=2007
--查询所有“计算机”开头的课程信息
select * from couse where cs_nm LIKE '计算机%'
--查询所有由三个字组成的“王”姓学生信息
select * from student where st_nm LIKE '王__'
--查询所有课程名中包含“信息”的课程信息
select * from couse where cs_nm LIKE '%信息%'
--查询学生姓名介于王姓到张姓的信息
select * from student where st_nm LIKE '[王-张]%'
--查询在1989.7.1到1990.6.30之间出生的学生信息
select st_id, st_nm, st_sex, st_birth from student where st_birth BETWEEN '1981.7.1' AND '1999.6.30'
--查询选修了1001号课程且成绩在60到80之间的学生选课信息
select * from slt_couse where cs_id='1001' and score BETWEEN 60 AND 80
--查询11系、12系、13系的学生信息
select * from student where st_dpid IN ('11', '12','13')
--查询所有张，王，李，赵姓的学生的学号、姓名、性别
select st_id, st_nm, st_sex from student where Left(st_nm,1) IN ('张','王','李','赵')
--查询所有生源为非空的学生信息
select * from  student where  st_from is not null
--查询选修了1001号课程且成绩为空的学生选课信息
select * from slt_couse where cs_id='1001' and score is null



use MySchoolDB
go
--查询课程信息，按课程名称降序排序
select * from couse order by cs_nm DESC
--查询选修了1001号课程成绩非空的学生学号和成绩，并按成绩降序排序
select st_id, score from slt_couse
where cs_id='1001' and score is not null
order by score desc
--查询11系学生学号、姓名和年龄，按年龄升序排序
select st_id,st_nm,st_dpid,DATEPART(yy,GETDATE( ))-DATEPART(yy,st_birth) as age
from  student
where st_dpid = '11'
order by age
--查询学生信息，按姓名升序排序，再按系号降序排序
select * from student order by st_nm, st_dpid desc
--创建学生表副本student01，仅保留学生学号、姓名和性别
select st_id, st_nm, st_sex into  student01  from  student
--查询陕西籍学生，将结果保存在新表st_shanxi
select * into st_shanxi
from  student
where  st_from='陕西%'
--查询选修了1001号课程学生的选课信息，按学号升序排序，将结果保存在新表slt1001
select * into slt1001 from slt_couse where cs_id='1001' order by st_id



use MySchoolDB
go
--查询课程总数
select COUNT(1) from couse
--查询选修1001号课程的学生人数
select  COUNT(st_id) from  slt_couse where cs_id = '1001'
--查询被选修课程的数量
select COUNT( DISTINCT cs_id ) from slt_couse
--查询选修070101班学生的平均入学成绩
select  AVG(st_score) from  student where  LEFT(st_id,6)='070101'
--查询070101001号学生选修课程的数量、总分以及平均分
select  COUNT(cs_id) AS 课程数量,SUM(score) AS 总分,AVG(score) AS 平均分 from  slt_couse where  st_id='070101001'
--查询选修1001号课程的学生人数、最高分、最低分和平均分
select COUNT(1) AS 学生人数,MAX(score) AS 最高分,MIN(score) AS 最低分,AVG (score) AS 平均分
from  slt_couse
where  cs_id='1001'
--求各个课程号和相应的选课人数
select  cs_id, COUNT(st_id) from  slt_couse group by cs_id
--统计各班人数
select LEFT(st_id,6) AS 班级, COUNT(1) AS 人数 from  student group by LEFT(st_id,6)
--依次按班级、系号对学生进行分类统计人数、入学平均分
select  st_dpid AS 系号, LEFT(st_id,6) AS 班级, 
COUNT(st_id) AS 人数, AVG(st_score) AS 均分
from  student  
group by LEFT(st_id,6), st_dpid
--查询选修了均分在75以上的课程号及均分
select cs_id AS 课程编号, AVG(score) AS 均分
from  slt_couse
group by cs_id 
having AVG(score)>75
--查询选修了2门以上课程的学生学号
select  st_id from slt_couse
group  by st_id 
having COUNT(*)>2
--明细汇总年龄<20的学生，并汇总学生数量、平均年龄
select  COUNT(1),AVG(DATEPART(yy,GETDATE())-DATEPART(yy,st_birth)) AS 年龄
from  student
where  DATEPART(yy,GETDATE())-DATEPART(yy,st_birth)<20
--按班级明细汇总成绩<85分的学生，汇总学生数、均分
select COUNT(st_nm), LEFT(st_id,6) AS 班级, AVG(st_score)
from  student
where  st_score<85
order  by 班级




use MySchoolDB
go
--用SQL Server形式连接查询学生学号、姓名、性别及其所选课程编号
select  a.st_id, st_nm, st_sex, cs_id
from  student a, slt_couse b
where a.st_id = b.st_id
order by  a.st_id
--用内连接查询学生学号、姓名、性别及其所选课程编号
select  a.st_id, st_nm, st_sex, cs_id
from  student a inner join slt_couse b on  a.st_id = b.st_id
order by a.st_id
--用SQL Server形式连接查询学生学号、姓名及其所选课程名称及成绩
select  a.st_id, st_nm, cs_nm, score
from  student a, slt_couse b, couse c
where  a.st_id = b.st_id AND b.cs_id = c.cs_id
order by a.st_id
--用内连接查询学生学号、姓名及其所选课程名称及成绩
select  a.st_id, st_nm, cs_nm, score
from  slt_couse a inner join student b on a.st_id = b.st_id
inner  join couse c on a.cs_id = c.cs_id
order by  b.st_id
--查询选修了1002课程的学生学号、姓名及1001课程成绩
select  a.st_id, st_nm, score
from  student a,slt_couse b
where  a.st_id = b.st_id and b.cs_id = '1002'
order by b.st_id
--查询选修了“数据结构”课程的学生学号、姓名及课程成绩
select  a.st_id, st_nm, score
from  student a, slt_couse b, couse c
where  a.st_id=b.st_id and b.cs_id=c.cs_id AND c.cs_nm='数据结构'
order by  a.st_id
--用左外连接查询没有选修任何课程的学生学号、姓名
select  a.st_id, st_nm, score
from  student a left outer join slt_couse b on a.st_id = b.st_id
where  b.cs_id is null
order by b.st_id
--用右外连接查询选修各个课程的学生学号
select  b.cs_id, a.st_id
from  slt_couse a right outer join couse b on a.cs_id = b.cs_id
order by b.cs_id